package com.example.demofx.master;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.stream.Collectors;

import java.util.logging.Level;
import java.util.logging.Logger;

public class GroupManager {
    private List<Group> groups; // 所有分组的列表
    private final String DB_URL = "jdbc:sqlite:" + System.getProperty("user.dir") + "/identifier.sqlite";
    private final ObservableList<Group> allGroups = FXCollections.observableArrayList();
//    private static final Logger LOGGER = Logger.getLogger(GroupManager.class.getName());

    public GroupManager() {
        this.groups = new ArrayList<>();
        loadGroups(); // 加载数据库中的组
    }

    // 获取所有分组
    public List<Group> getGroups() {
        return groups;
    }

    // 添加分组
    public boolean addGroup(Group group) {
        // 校验分组对象及组名是否有效
        if (group == null || group.getGroupName() == null || group.getGroupName().isEmpty()) {
            throw new IllegalArgumentException("组名不能为空");
        }
        if (isGroupNameExists(group.getGroupName())) {// 校验分组对象及组名是否已存在
            throw new IllegalArgumentException("组名已存在");
        }
        String sql = "INSERT INTO groups (name) VALUES (?)";
        // 尝试获取数据库连接并执行插入操作
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            pstmt.setString(1, group.getGroupName());
            int rowsAffected = pstmt.executeUpdate();
            if (rowsAffected > 0) {
                ResultSet rs = pstmt.getGeneratedKeys();// 获取自动生成的主键ID
                if (rs.next()) {
                    int generatedId = rs.getInt(1);
                    group.setGroupId(generatedId); // 将生成的ID设置到分组对象
                    groups.add(group);// 将分组添加到内存列表
                    return true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 从系统中删除指定分组
    public boolean removeGroup(Group group) {
        if (group == null || group.getGroupId() == -1) {
            throw new IllegalArgumentException("无效的组对象");
        }
        // 删除 contact_group_mapping 中的关联
        String deleteMappingSql = "DELETE FROM contact_group_mapping WHERE group_id = ?";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement mappingPstmt = conn.prepareStatement(deleteMappingSql)) {
            mappingPstmt.setInt(1, group.getGroupId());
            mappingPstmt.executeUpdate(); // 执行删除操作
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // 删除 groups 表中的分组
        String deleteGroupSql = "DELETE FROM groups WHERE id = ?";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement groupPstmt = conn.prepareStatement(deleteGroupSql)) {
            groupPstmt.setInt(1, group.getGroupId());
            int rowsAffected = groupPstmt.executeUpdate();// 执行删除并获取受影响行数
            if (rowsAffected > 0) {
                groups.remove(group);// 从内存列表中移除分组
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 检查指定组名是否已存在
    // @return 存在返回 true，不存在返回 false
    public boolean isGroupNameExists(String groupName) {
        return getAllGroups(new ContactManager()).stream()
                .anyMatch(group -> group.getGroupName().equals(groupName));
    }

    // 将联系人添加到组
    public boolean addContactToGroup(Contact contact, Group group) {
        if (contact == null || group == null || group.getGroupId() == -1) {
            throw new IllegalArgumentException("无效的联系人或组对象");
        }

        String sql = "INSERT INTO contact_group_mapping (contact_id, group_id) VALUES (?, ?)";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, contact.getId());
            pstmt.setInt(2, group.getGroupId());
            int rowsAffected = pstmt.executeUpdate();

            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 将联系人从组移除
    public boolean removeContactFromGroup(Contact contact, Group group) {
        if (contact == null || group == null || group.getGroupId() == -1) {
            throw new IllegalArgumentException("无效的联系人或组对象");
        }

        String sql = "DELETE FROM contact_group_mapping WHERE contact_id = ? AND group_id = ?";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, contact.getId());
            pstmt.setInt(2, group.getGroupId());
            int rowsAffected = pstmt.executeUpdate();

            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 加载所有分组
    private void loadGroups() {
        String sql = "SELECT * FROM groups";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                int groupId = rs.getInt("id");
                String groupName = rs.getString("name");
                groups.add(new Group(groupId, groupName));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 移除联系人与所有组的关联
    public void removeContactFromAllGroups(Contact contact) {
        String sql = "DELETE FROM contact_group_mapping WHERE contact_id = ?";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, contact.getId());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 获取所有有效分组并附带联系人统计信息
    // @return 返回包含所有有效分组的 ObservableList<Group>
    public ObservableList<Group> getAllGroups(ContactManager contactManager) {
        loadGroupsFromDB(); // 从数据库加载
        updateGroupStats(contactManager); // 更新统计
        return allGroups.filtered(g -> !g.isDeleted());
    }

    // 从数据库加载所有分组信息到 ObservableList
    // @return 无返回值，将数据库中的分组加载到 allGroups 列表
    private void loadGroupsFromDB() {
        allGroups.clear();
        String sql = "SELECT id, name FROM groups";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Group group = new Group();
                group.setGroupId(rs.getInt("id"));
                group.setGroupName(rs.getString("name"));
                allGroups.add(group);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 更新所有分组的联系人统计信息
    // @return 无返回值，为每个分组设置联系人数量
    private void updateGroupStats(ContactManager contactManager) {
        Map<Integer, Long> groupCounts = contactManager.getAllContacts().stream()
                .flatMap(c -> c.getGroups().stream())
                .collect(Collectors.groupingBy(Group::getGroupId, Collectors.counting()));

        allGroups.forEach(group ->
                group.setContactCount(groupCounts.getOrDefault(group.getGroupId(), 0L).intValue())
        );
    }

    // 更新组信息（主要用于刷新联系人数量统计）
    // @return 无返回值，更新组信息并刷新统计
    public void updateGroup(Group group, ContactManager contactManager) {
        if (group == null || group.getGroupId() == -1) {
            throw new IllegalArgumentException("无效的组对象");
        }

        // 从数据库重新加载所有组（可优化为只查询特定组的联系人数量）
        loadGroupsFromDB();

        // 更新所有组的统计信息
        updateGroupStats(contactManager);

    }

    // 根据关键词搜索分组（支持组名和联系人数量搜索）
    // @return 返回符合搜索条件的分组
    public ObservableList<Group> searchGroups(String keyword, ContactManager contactManager) {
        return getAllGroups(contactManager).stream()
                .filter(g -> g.getGroupName().toLowerCase().contains(keyword.toLowerCase()) ||
                        String.valueOf(g.getContactCount()).contains(keyword))
                .collect(Collectors.toCollection(FXCollections::observableArrayList));
    }
}